use emode
go
select
	  IDENTITY(int, 1, 1) as AGG1_ID
	, req.YR
	, req.QTR
	, req.MTH
	, req.MONTH_NAME
	, req.CITY
	, req.STORE_NAME
	, req.SALES_REVENUE
	, req.QUANTITY_SOLD
	, req.MARGIN
into
	[dbo].[AGG_YR_QT_MT_MN_WK_RG_CY_SN_SR_QT_MA]
from
	(
	select
		  distinct sh.WEEK_KEY as WK
		, cal.YEAR as YR
		, cal.QUARTER as QTR
		, cal.month as MTH
		, cal.month_name as MONTH_NAME
		, ol.CITY as CITY
		, ol.SHOP_NAME as STORE_NAME
		, sum(sh.amount_sold) over (partition by sh.week_key) as SALES_REVENUE
		, sum(sh.quantity_sold) over (partition by sh.week_key) as QUANTITY_SOLD
		, sum(sh.margin) over (partition by sh.week_key) as MARGIN
	from
		calendar_year_lookup cal
		inner join shop_facts sh
			on cal.week_key = sh.week_key
		inner join OUTLET_LOOKUP ol
			on sh.SHOP_CODE = ol.SHOP_CODE
	) req
order by
	req.WK asc
Go
	
use emode
go
select
	  IDENTITY(int, 1, 1) as AGG2_ID
	, req.YR
	, req.QTR
	, req.STATE
	, req.LINE
	, req.CATEGORY
	, req.SALES_REVENUE
into
	[dbo].[AGG_YR_QT_M_ST_LN_CA_SR]
from
	(
	select
		  cal.YEAR as YR
		, cal.QUARTER as QTR
		, ol.STATE as STATE
		, acl.FAMILY_NAME LINE
		, acl.CATEGORY CATEGORY
		, sum(sh.amount_sold) as SALES_REVENUE
	from
		calendar_year_lookup cal
		inner join shop_facts sh
			on cal.week_key = sh.week_key
		inner join OUTLET_LOOKUP ol
			on sh.SHOP_CODE = ol.SHOP_CODE
		inner join ARTICLE_COLOR_LOOKUP acl
			on sh.ARTICLE_CODE = acl.ARTICLE_CODE
	group by
		cal.YEAR 
		, cal.QUARTER 
		, ol.STATE 
		, acl.FAMILY_NAME 
		, acl.CATEGORY
	) req
order by
	  req.YR
	, req.QTR
	, req.STATE
	, req.LINE
	, req.CATEGORY

Go
ALTER TABLE [dbo].[AGG_YR_QT_MT_MN_WK_RG_CY_SN_SR_QT_MA] ADD PRIMARY KEY (AGG1_ID)

Go
ALTER TABLE [dbo].[AGG_YR_QT_M_ST_LN_CA_SR] ADD PRIMARY KEY (AGG2_ID)